using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Xml;
using System.IO;
using System.Text;
using System.Xml.XPath;
using System.Xml.Xsl;

public partial class menu : System.Web.UI.UserControl
{
    protected void Page_PreRender(object sender, EventArgs e)
    {
        litMenu.Text = ExecuteXSLTransformation();
    }

    public string GenerateXmlFormat()
    {
        string SqlCommand;
        DataSet DbMenu;
        DataRelation relation;

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString))
        {

            SqlCommand = "Select MenuID, Text, Description, ParentID from Menu";

            DbMenu = new DataSet();

            SqlDataAdapter Adapter = new SqlDataAdapter(SqlCommand, conn);

            Adapter.Fill(DbMenu);

            Adapter.Dispose();
        }

        DbMenu.DataSetName = "Menus";

        DbMenu.Tables[0].TableName = "Menu";

        //create Relation Parent and Child
        relation = new DataRelation("ParentChild", DbMenu.Tables["Menu"].Columns["MenuID"], DbMenu.Tables["Menu"].Columns["ParentID"], true);

        relation.Nested = true;

        DbMenu.Relations.Add(relation);

        return DbMenu.GetXml();
    }

    public string ExecuteXSLTransformation()
    {
        string HtmlTags, XsltPath;
        MemoryStream DataStream = default(MemoryStream);
        StreamReader streamReader = default(StreamReader);

        try
        {
            //Path of XSLT file
            XsltPath = HttpContext.Current.Server.MapPath("XsltFormatFolder/TransformXSLT.xsl");

            //Encode all Xml format string to bytes
            byte[] bytes = Encoding.ASCII.GetBytes(GenerateXmlFormat());

            DataStream = new MemoryStream(bytes);

            //Create Xmlreader from memory stream
            XmlReader reader = XmlReader.Create(DataStream);

            // Load the XML
            XPathDocument document = new XPathDocument(reader);

            XslCompiledTransform XsltFormat = new XslCompiledTransform();

            // Load the style sheet.
            XsltFormat.Load(XsltPath);

            DataStream = new MemoryStream();

            XmlTextWriter writer = new XmlTextWriter(DataStream, Encoding.ASCII);

            //Apply transformation from xml format to html format and save it in xmltextwriter
            XsltFormat.Transform(document, writer);

            streamReader = new StreamReader(DataStream);

            DataStream.Position = 0;

            HtmlTags = streamReader.ReadToEnd();

            return HtmlTags;
        }
        finally
        {
            //Release the resources
            streamReader.Close();
            DataStream.Close();
        }

    }
}
